const sql = require('mssql');

/**
 * 
 * 基础资料的手动货位架管理
 */

/**
 * 
 * 初始化表一的信息 
 */
const getCargoRackShopInfor1 = async (ctx, next) => {
    const body = ctx.request.body;
    let totalSql = `SELECT COUNT(B.BM) AS TOTAL FROM BM_YP B WHERE QY_FLAG = 'T'`;
    // let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY B.BM) AS px,B.BM,B.TM,B.MC,B.GG,B.JXMC,B.JLDW,B.SCCJ,B.PZWH,B.YPLX,B.CCTJ,B.LB FROM BM_YP B WHERE B.QY_FLAG = 'T'`;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY B.BM) AS px,B.BM,B.TM,B.MC,B.GG,B.JXMC,B.JLDW,B.SCCJ,B.PZWH,B.YPLX,B.CCTJ,B.LB FROM BM_YP B  WHERE QY_FLAG = 'T' `;
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 10 + 1} and ${body.pageNum * 10}`;
    if (body.searchValList.ypbm) {
        startSql += ` AND B.BM LIKE '%${body.searchValList.ypbm}%' OR B.TM LIKE '%${body.searchValList.ypbm}%' OR B.JP LIKE '%${body.searchValList.ypbm}%'`;
        totalSql += ` AND B.BM LIKE '%${body.searchValList.ypbm}%' OR B.TM LIKE '%${body.searchValList.ypbm}%' OR B.JP LIKE '%${body.searchValList.ypbm}%'`;
    }
    startSql += endSql;
    let total = await sql.query(totalSql);
    let result = await sql.query(startSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset }
}
/**
 * 
 * 初始化表二的信息 
 */
const getCargoRackShopInfor2 = async (ctx, next) => {
    const body = ctx.request.body;
    let totalSql = `SELECT COUNT(B.BM) AS TOTAL FROM BM_YP B LEFT JOIN kc_kcdtb K ON B.BM = K.YPBM WHERE K.KCSL > 0 AND  K.BMBM = '07' AND B.QY_FLAG = 'T' `;
    let startSql = `SELECT * FROM (SELECT ROW_NUMBER() OVER(ORDER BY B.BM) AS px,B.BM,B.MC,B.GG,B.JXMC,B.JLDW,B.SCCJ,K.YXQZ,K.KCSL,K.SCPH,K.HWBH,K.BZ FROM BM_YP B LEFT JOIN KC_KCDTB K ON B.BM = K.YPBM  WHERE K.KCSL > 0 AND K.BMBM = '07' AND B.QY_FLAG = 'T' `;
    let endSql = `) AS t1 WHERE t1.px between ${(body.pageNum - 1) * 10 + 1} and ${body.pageNum * 10}`;
    if (body.searchValList.ckmc) {
        startSql += ` AND K.CKBM = '${body.searchValList.ckmc}'`;
        totalSql += ` AND K.CKBM = '${body.searchValList.ckmc}'`;
    }
    if (body.chooseVal) {
        startSql += ` AND K.YPBM = '${body.chooseVal}'`;
        totalSql += ` AND K.YPBM = '${body.chooseVal}'`;
    }
    if (body.searchValList.hwbh) {
        startSql += ` AND K.HWBH LIKE '%${body.searchValList.hwbh}%'`;
        totalSql += ` AND K.HWBH LIKE '%${body.searchValList.hwbh}%'`;
    }
    // if (body.searchValList.tm) {
    //     startSql += ` AND B.TM LIKE '%${body.searchValList.tm}%'`;
    //     totalSql += ` AND B.TM LIKE '%${body.searchValList.tm}%'`;
    // }
    // if (body.searchValList.jp) {
    //     startSql += ` AND B.JP LIKE '%${body.searchValList.jp}%'`;
    //     totalSql += ` AND B.JP LIKE '%${body.searchValList.jp}%'`;
    // }
    if (body.searchValList.isHwbm == 1) {
        startSql += ` AND (K.HWBH IS NULL OR K.HWBH = '')`;
        totalSql += ` AND (K.HWBH IS NULL OR K.HWBH = '')`;
    }
    if (body.searchValList.isHwbm == 2) {
        startSql += ` AND K.HWBH IS NOT NULL AND K.HWBH != ''`;
        totalSql += ` AND K.HWBH IS NOT NULL AND K.HWBH != ''`;
    }
    startSql += endSql;
    let total = await sql.query(totalSql);
    let result = await sql.query(startSql);
    ctx.body = { total: total.recordset[0].TOTAL, result: result.recordset }
}
/**
 * 
 * 添加货架位
 */
const addCargoRack = async (ctx, next) => {
    const body = ctx.request.body;
    let iscargoRackNumSql = `SELECT * from KC_KCDTB WHERE HWBH = '${body.cargoRackNum}' AND BMBM ='07'`;
    let iscargoRackNum = await sql.query(iscargoRackNumSql);
    if (iscargoRackNum.rowsAffected > 0) {
        ctx.body = { success: 1 }
    } else {
        let insertSql = `UPDATE KC_KCDTB SET HWBH='${body.cargoRackNum}' WHERE YPBM = '${body.bm}'`
        let result = await sql.query(insertSql);
        ctx.body = { success: 3 }
    }
}

/**
 * 
 * 清空库存小于等于零的货位编号 
 */
const clearCargoRack = async (ctx, next) => {
    let clearSql = `UPDATE KC_KCDTB SET HWBH= '' WHERE KCSL <= 0 OR KCSL IS NULL`;
    sql.query(clearSql);
    ctx.body = { success: true };
}

/**
 * 
 * 修改条码 
 */
const correctCargoRackTm = async (ctx, next) => {
    const body = ctx.request.body;
    let updateSql = `UPDATE BM_YP SET TM= '${body.tm}' WHERE BM='${body.bm}'`;
    let result = await sql.query(updateSql);
    let success = result.rowsAffected[0] > 0 ? true : false;
    ctx.body = { success: success };
}
/**
 * 
 * 清空选中的货位架 
 */
const clearChooseCargoRack = async (ctx, next) => {
    const body = ctx.request.body;
    let updateSql = `UPDATE KC_KCDTB SET HWBH= '' WHERE YPBM ='${body.chooseVal}'`;
    let result = await sql.query(updateSql);
    let success = result.rowsAffected[0] > 0 ? true : false;
    ctx.body = { success: success };
}

/**
 * 获取商品编码相同的货位架
 */
const fuseCargoRack = async (ctx, next) => {
    let hasHwbmShopSql = `SELECT YPBM,HWBH  FROM KC_KCDTB WHERE KCSL>0 AND BMBM = '07'`;
    let hwbmShopListResult = await sql.query(hasHwbmShopSql);
    let hwbmShopList = hwbmShopListResult.recordset;
    let newHwbmShopList = {};
    hwbmShopList.forEach(item => {
        if (!Array.isArray(newHwbmShopList[item.YPBM])) {
            newHwbmShopList[item.YPBM] = []
        }
        newHwbmShopList[item.YPBM].push(item.HWBH);

    });
    const judgeArr = (arr) => {
        let result = '';
        let empty = true;
        arr.forEach(item => {
            if (item) {
                result = item;
            } else {
                empty = item;
            }
        })

        return !empty ? result : '';
    }
    let ypbmList = [];
    for (let item in newHwbmShopList) {
        if (newHwbmShopList[item].length > 1) {
            let judgeReult = judgeArr(newHwbmShopList[item]);
            if (judgeReult) {
                ypbmList.push(item);
            }
        }
    }
    ctx.body = { result: ypbmList };
}




module.exports = {
    'POST /getCargoRackShopInfor1': getCargoRackShopInfor1,
    'POST /getCargoRackShopInfor2': getCargoRackShopInfor2,
    'POST /addCargoRack': addCargoRack,
    'POST /clearCargoRack': clearCargoRack,
    'POST /clearChooseCargoRack': clearChooseCargoRack,
    'POST /correctCargoRackTm': correctCargoRackTm,
    'GET /fuseCargoRack': fuseCargoRack
}